package cn.ml.web3.dao;



import cn.ml.web3.commons.DBhelper;
import cn.ml.web3.entity.Pagination;
import cn.ml.web3.entity.Student;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class StudentDaoImpl implements StudentDao  {
    @Override
    public void save(Student student) {

        //insert into xueyuan2(name,gender,bornday)value('"+name+"','"+gender+"','"+bornday+"')
        String sql = "insert into xueyuan2(name,gender,bornday) values(?,?,?)";
        List<Object> params = new ArrayList<>();
        params.add(student.getName());
        params.add(student.getGender());
        params.add(student.getBornday());
        //open Connection
        Connection conn = null;
        try {
            conn = DBhelper.getConnection();
            DBhelper.execute(conn,sql,params);
            sql = "select LAST_INSERT_ID() as id";
            DBhelper.executeQuery(conn,sql, null, rs -> {
                if (rs.next()) {
                    student.setId(rs.getInt("id"));
                }
            });
        }catch(Exception e){
            e.printStackTrace();
        }finally {
            DBhelper.close(conn);
        }
        //close connection
    }

    @Override
    public Student get(int id) {
        String sql = "select * from xueyuan2 where id = ?";
        List<Object> params = new ArrayList<>();
        params.add(id);
        Student student = new Student();
        try (Connection conn = DBhelper.getConnection()) {
            DBhelper.executeQuery(conn, sql, params, rs -> {
                if (rs.next()) {
                    student.setId(rs.getInt("id"));
                    student.setName(rs.getString("name"));
                    student.setGender(rs.getString("gender"));
                    student.setBornday(rs.getTimestamp("bornday"));
                }
            });

        } catch (IOException | ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return student;
    }


    @Override
    public List<Student> list() {
        List<Student> list = new ArrayList<>();
        String sql = "select * from xueyuan2";
        try (Connection conn = DBhelper.getConnection()) {
            DBhelper.executeQuery(conn, sql, null, new DBhelper.CallBack() {
                @Override
                public void work(ResultSet rs) throws SQLException {
                    while (rs.next()) {
                        list.add(new Student(
                                rs.getInt("id"),
                                rs.getString("name"),
                                rs.getString("gender"),
                                rs.getTimestamp("bornday")
                        ));
                    }
                }
            });
        } catch (IOException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return list;
    }


    public List<Student> findByQuery(String sql, Object... params) {
        return null;
    }

    @Override
    public void update(Student student) {
        String sql = "update xueyuan2 set name = ?,gender = ?,bornday = ? where id = ?";
        List params = new ArrayList();
        params.add(student.getName());
        params.add(student.getGender());
        params.add(student.getBornday());
        params.add(student.getId());
        try (Connection conn = DBhelper.getConnection()) {
            DBhelper.execute(conn,sql,params);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void delete(int id) {
        String sql ="delete from xueyuan2 where id = ?";
        List<Object> params = new ArrayList<>();
        params.add(id);
        try (Connection conn = DBhelper.getConnection()) {
            DBhelper.execute(conn,sql,params);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public void listByPagination(Pagination<Student> pagination) {
        try (Connection connection = DBhelper.getConnection()) {

            //查询总记录数
            String sql = "select count(1) as total from xueyuan2";
            DBhelper.executeQuery(connection, sql, null, new DBhelper.CallBack() {
                @Override
                public void work(ResultSet rs) throws SQLException {
                    if(rs.next()){
                        pagination.setTotalCount(rs.getInt("total"));
                    }
                }
            });
            //查询当前页应该现实的记录
            int offset = (pagination.getCurrentPageIndex()-1)*pagination.getPageSize();
            int size = pagination.getPageSize();
            sql = "select * from xueyuan2 limit "+offset+","+size;
            DBhelper.executeQuery(connection, sql, null, new DBhelper.CallBack() {
                @Override
                public void work(ResultSet rs) throws SQLException {
                    List<Student> students = new ArrayList<>();
                    while (rs.next()){
                        students.add(
                                new Student(
                                        rs.getInt("id"),
                                        rs.getString("name"),
                                        rs.getString("gender"),
                                        rs.getTimestamp("bornday")
                                )
                        );
                    }
                    pagination.setResult(students);
                }
            });


        } catch (IOException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

    }


    public void listByPaginationAndParams(Pagination<Student> pagination, Map<String, String> params) {

        //查询总记录数
        String sql1 = "select count(1) as total from xueyuan2 where 1 = 1 ";
        String sql2 = "select * from xueyuan2 where 1 = 1 ";
        String where = "";
        if(params.get("likeName")!=null){
            where+=" and name like '%"+params.get("likeName")+"%' " ;
        }
        if(params.get("gender")!=null){
            where += " and gender = '"+params.get("gender")+"' ";
        }
        if(params.get("start")!=null){
            where +=" and bornday > '"+params.get("start")+"' ";
        }
        if(params.get("end")!=null){
            where +=" and bornday < '"+params.get("end")+"' ";
        }


        int offset = (pagination.getCurrentPageIndex()-1)*pagination.getPageSize();
        int size = pagination.getPageSize();
        sql1 +=where;
        sql2 +=where + " limit "+offset+","+size;

        try(Connection connection = DBhelper.getConnection()){
            DBhelper.executeQuery(connection, sql1, null, new DBhelper.CallBack() {
                @Override
                public void work(ResultSet rs) throws SQLException {
                    if(rs.next()) {
                        pagination.setTotalCount(rs.getInt("total"));
                    }
                }
            });
            DBhelper.executeQuery(connection, sql2, null, new DBhelper.CallBack() {
                @Override
                public void work(ResultSet rs) throws SQLException {
                    List<Student> students = new ArrayList<>();
                    while (rs.next()){
                        students.add(
                                new Student(
                                        rs.getInt("id"),
                                        rs.getString("name"),
                                        rs.getString("gender"),
                                        rs.getTimestamp("bornday")
                                )
                        );
                    }
                    pagination.setResult(students);
                }
            });

        } catch (IOException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }




}
